package com.example.mybatis.dao;

import com.example.mybatis.pojo.Grade;
import com.example.mybatis.pojo.Results;
import com.example.mybatis.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface UserDao {
    //    此处利用注解开发

    //    查询所有用户
    @Select("select * from user")
    List<User> userList();

    //    新增用户
    @Insert("insert into user (mail, username, userpwd, sex, idcard, grade, level) values (#{mail}, #{username}, #{userpwd}, #{sex}, #{idcard}, #{grade}, #{level})")
    int userAdd(Map<String, Object> map);

    //    通过mail查询
    @Select("select * from user where mail = #{mail}")
    List<User> mailList(String mail);

    //    通过idcard查询查询
    @Select("select * from user where idcard = #{idcard}")
    List<User> idcardList(String idcard);

    //    通过mail删除用户
    @Delete("delete from user where mail = #{mail}")
    int delectmail(String mail);

    //    通过mail修改用户密码
    @Update("update user set userpwd = #{pwd} where mail = #{mail}")
    int ModifyPwd(String mail, String pwd);

    //    查询当前班级的学生
    @Select("select * from user where grade=#{grade} and level = 2")
    List<User> showclassstudent(String grade);

    //    查询所有教师
    @Select("select * from user where level = 1")
    List<User> showTeacher();

    //    添加学生成绩
    @Insert("insert into results (username,grade,subjects,results) values (#{username},#{grade},#{subjects},#{results})")
    int Resutls(Map<String, Object> map);

    //    获取科目
    @Select("select grade from grade")
    List<Grade> showGrade();

    //    查询指定学生的成绩
    @Select("select * from results where username = #{username} and grade = #{grade} and subjects = #{subjects}")
    List<Results> showresults(String username, String grade, String subjects);

    //    查询所有成绩信息
    @Select("select * from results")
    List<Results> showallresults();

    //      查询指定班级的所有成绩信息
    @Select("select * from results where grade = #{grade}")
    List<Results> showgraderesults(String grade);

    //删除指定班级、科目、名称的成绩信息
    @Delete("delete from results where username = #{username} and grade = #{grade} and  subjects= #{subjects}")
    int delectresults(String username, String grade, String subjects);

    //    查询指定姓名，班级的成绩信息
    @Select("select * from results where username = #{username} and grade = #{grade}")
    List<Results> showusernamegrade(String username, String grade);

    //    查询所有学生信息
    @Select("select * from user where level = 2")
    List<User> showstudent();
}
